package edu.columbia.sportsleagueandroid.server;

import edu.columbia.sportsleagueandroid.appengine.beans.MatchInning;
import edu.columbia.sportsleagueandroid.appengine.beans.MatchOver;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class ScoreboardActions
{
	private Connection dbConnection;
	private static String MATCH_INNINGS_TABLE = "match_innings";
	private static String PLAYER = "player";
	private static String MATCH_OVER_TABLE = "match_inning_over";
	
	public ScoreboardActions(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
	public int UpdateInning(MatchInning matchInning)
	{
		String updateStatement = "INSERT INTO "+MATCH_INNINGS_TABLE+" VALUES (?,?,?,?)";
		int success = -1;
		
		try 
		{
			PreparedStatement preparedStatement = dbConnection.prepareStatement(updateStatement);
			
			preparedStatement.setLong(1, matchInning.getMatchNumber());
			preparedStatement.setInt(2, matchInning.getInningNumber());
			preparedStatement.setString(3, matchInning.getBattingTeam());
			preparedStatement.setString(4, matchInning.getFieldingTeam());
			
			success = preparedStatement.executeUpdate();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		return success;
	}
	
	public int FetchScore(MatchOver matchOver)
	{
		int score = 0;
		
		String fetchStatement = "SELECT sum(runs) as score from "+MATCH_OVER_TABLE+" where match_id = "+
		matchOver.getMatchNumber()+" and inning_id = "+matchOver.getInningNumber();
		
		ResultSet rs;
		
		try 
		{
			Statement scoreFetchStatement = dbConnection.createStatement();
			rs = scoreFetchStatement.executeQuery(fetchStatement);
			
			while(rs.next())
			{
				score = rs.getInt("score");
			}

		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return score;
	}
	
	public int UpdateStadium(Stadium stadium)
	{
		String updateStatement = "insert into stadium values(?,?,?,?)";
		int success = -1;
		
		try
		{
			PreparedStatement preparedStatement = dbConnection.prepareStatement(updateStatement);
			
			preparedStatement.setString(1,stadium.getStadiumName());
			preparedStatement.setString(2, stadium.getStadiumCity());
			preparedStatement.setString(3, stadium.getStadiumCountry());
			preparedStatement.setLong(4, stadium.getStadiumCapacity());
			
			success = preparedStatement.executeUpdate();
			
			
			
			dbConnection.commit();
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
		}
		
		return success;
	}
	
	public int UpdateScorecard(MatchOver matchOver)
	{
		String updateStatement = "";
		int success = -1;
		
		try
		{
			PreparedStatement preparedStatement = dbConnection.prepareStatement(updateStatement);
			
			preparedStatement.setLong(1, matchOver.getMatchNumber());
			preparedStatement.setInt(2, matchOver.getOverNumber());
			preparedStatement.setInt(3, matchOver.getBallNumber());
			preparedStatement.setTimestamp(4, Timestamp.valueOf(matchOver.getBallTime()));
			preparedStatement.setString(5, matchOver.getAction());
			preparedStatement.setString(6, matchOver.getBatsman());
			preparedStatement.setString(7, matchOver.getBowler());
			preparedStatement.setString(8, matchOver.getComment());
			preparedStatement.setInt(9, matchOver.getRunsScored());
			
			success = preparedStatement.executeUpdate();
			
			if(matchOver.getRunsScored() != 0 && !(matchOver.getAction().equalsIgnoreCase("WB") ||
					matchOver.getAction().equalsIgnoreCase("NB")))
			{
				updateStatement = "UPDATE "+PLAYER+" SET runs = runs + ? WHERE player_name = ?";
				preparedStatement = dbConnection.prepareStatement(updateStatement);
				preparedStatement.setInt(1, matchOver.getRunsScored());
				preparedStatement.setString(2, matchOver.getBatsman());
				
				success = preparedStatement.executeUpdate();
			}
			else if(matchOver.getAction().equalsIgnoreCase("W"))
			{
				updateStatement = "UPDATE "+PLAYER+" SET wickets = wickets + 1 WHERE player_name = ?";
				preparedStatement = dbConnection.prepareStatement(updateStatement);
				preparedStatement.setString(1, matchOver.getBowler());
				
				success = preparedStatement.executeUpdate();
			}
			else
			{}
			dbConnection.commit();
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
		}
		
		return success;
	}
}
